﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NUnit.Framework;
using ServiceStack.DataAnnotations;
using ServiceStack.Model;
using ServiceStack.OrmLite.Legacy;
using ServiceStack.OrmLite.MySql.Converters;
using ServiceStack.Text;

namespace ServiceStack.OrmLite.MySql.Tests;

[TestFixture]
public class DateTimeColumnTest
    : OrmLiteTestBase
{
    [Test]
    public void Can_create_table_containing_DateTime_column()
    {
        using var db = OpenDbConnection();
        db.CreateTable<Analyze>(true);
    }

    [Test]
    public void Can_store_DateTime_Value()
    {
        using var db = OpenDbConnection();
        db.CreateTable<Analyze>(true);

        var obj = new Analyze {
            Id = 1,
            Date = DateTime.Now,
            Url = "http://www.google.com"
        };

        db.Save(obj);
    }

    [Test]
    public void Can_store_and_retrieve_DateTime_Value()
    {
        using var db = OpenDbConnection();
        db.CreateTable<Analyze>(true);

        var obj = new Analyze {
            Id = 1,
            Date = DateTime.Now,
            Url = "http://www.google.com"
        };

        db.Save(obj);

        var id = (int)db.LastInsertId();
        var target = db.SingleById<Analyze>(id);

        Assert.That(target, Is.Not.Null);
        Assert.That(target.Id, Is.EqualTo(id));
        Assert.That(target.Date, Is.EqualTo(obj.Date).Within(TimeSpan.FromSeconds(1)));
        Assert.That(target.Url, Is.EqualTo(obj.Url));
    }

    [Test]
    public void Can_change_DateTime_precision()
    {
        using var db = OpenDbConnection();
#if MYSQLCONNECTOR
            if (MySqlConnectorDialect.Provider.GetConverter(typeof(DateTime)) is MySqlDateTimeConverterBase dateConverter)
            {
                dateConverter.Precision = 3;
            }
#else
        if (MySqlDialect.Provider.GetConverter(typeof(DateTime)) is MySqlDateTimeConverterBase dateConverter)
        {
            dateConverter.Precision = 3;
        }
#endif

        OrmLiteUtils.PrintSql();
        db.CreateTable<Analyze>(true);

        var obj = new Analyze {
            Id = 1,
            Date = DateTime.Now,
            Url = "http://www.google.com"
        };

        db.Save(obj);

        var id = (int)db.LastInsertId();
        var target = db.SingleById<Analyze>(id);
        target.PrintDump();

        var q = db.From<Analyze>()
            .Where(x => x.Date >= DateTime.Now.Date);
        var results = db.Select(q);
        Assert.That(results.Count, Is.EqualTo(1));
        results.PrintDump();
            
        results = db.SelectFmt<Analyze>("AnalyzeDate >= {0}", DateTime.Now.AddDays(-1));
        results.PrintDump();
    }

    [Test]
    public void Can_query_DateTime_column_in_select_without_any_format_parameter()
    {
        using var db = OpenDbConnection();
        db.CreateTable<Analyze>(true);
        var obj = new Analyze
        {
            Id = 1,
            Date = DateTime.Now,
            Url = "http://www.google.com"
        };
        db.Save(obj);

        var q=db.From<Analyze>().Select(i => i.Date);
        var sql = q.ToMergedParamsSelectStatement();
        var val= db.Select<DateTime>(q).First();
        Assert.That(obj.Date,Is.EqualTo(val).Within(TimeSpan.FromSeconds(1)));
    }

    [Test]
    public void Can_query_DateTime_column_in_select_with_format_parameter()
    {
        using var db = OpenDbConnection();
        db.CreateTable<Analyze>(true);
        var obj = new Analyze
        {
            Id = 1,
            Date = DateTime.Now,
            Url = "http://www.google.com"
        };
        db.Save(obj);
        var q = db.From<Analyze>().Select(i => i.Date.ToString("%Y-%m-%d %H:%i:%s"));
        var sql = q.ToMergedParamsSelectStatement();
        var val = db.Select<string>(q).First();
        Assert.That(obj.Date, Is.EqualTo(DateTime.Parse(val)).Within(TimeSpan.FromSeconds(1)));
    }

    [Test]
    public void Can_query_DateTime_column_in_where_with_format_parameter()
    {
        using var db = OpenDbConnection();
        db.CreateTable<Analyze>(true);
        var obj = new Analyze
        {
            Id = 1,
            Date = DateTime.Now,
            Url = "http://www.google.com"
        };
        var yesterdayString = obj.Date.AddDays(-1).ToString("yyyy-MM-dd");
        var todayString = obj.Date.ToString("yyyy-MM-dd");
        db.Save(obj);
        var q = db.From<Analyze>().Where(i=>i.Date.ToString("%Y-%m-%d")== yesterdayString);
        var results = db.Select<Analyze>(q);
        var sql = q.ToMergedParamsSelectStatement();
        Assert.That(results.Count, Is.EqualTo(0));

        q = db.From<Analyze>().Where(i => i.Date.ToString("%Y-%m-%d") == todayString);
        results = db.Select<Analyze>(q);
        sql = q.ToMergedParamsSelectStatement();
        Assert.That(results.Count, Is.EqualTo(1));

        var id = (int)db.LastInsertId();
        var target = db.SingleById<Analyze>(id);
        Assert.That(target, Is.Not.Null);
        Assert.That(target.Id, Is.EqualTo(id));
        Assert.That(target.Date, Is.EqualTo(obj.Date).Within(TimeSpan.FromSeconds(1)));
        Assert.That(target.Url, Is.EqualTo(obj.Url));
    }

    [Test]
    public void Can_query_DateTime_column_in_select_and_where_with_format_parameter()
    {
        using var db = OpenDbConnection();
        db.CreateTable<Analyze>(true);
        var obj = new Analyze
        {
            Id = 1,
            Date = DateTime.Now,
            Url = "http://www.google.com"
        };
        var yesterdayString = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd");
        var todayString = DateTime.Now.ToString("yyyy-MM-dd");
        db.Save(obj);
        var q = db.From<Analyze>().Where(i => i.Date.ToString("%Y-%m-%d") == yesterdayString).Select(i=>i.Date.ToString("%Y-%m-%d"));
        var results = db.Select<string>(q);
        var sql = q.ToMergedParamsSelectStatement();
        Assert.That(results.Count, Is.EqualTo(0));

        q = db.From<Analyze>().Where(i => i.Date.ToString() == yesterdayString).Select(i => i.Date.ToString("%Y-%m-%d"));
        results = db.Select<string>(q);
        sql = q.ToMergedParamsSelectStatement();
        Assert.That(results.Count, Is.EqualTo(0));

        q = db.From<Analyze>().Where(i => i.Date.ToString("%Y-%m-%d") == todayString).Select(i => i.Date.ToString("%Y-%m-%d"));
        results = db.Select<string>(q);
        sql = q.ToMergedParamsSelectStatement();
        Assert.That(results.Count, Is.EqualTo(1));

        var target = results.First();
        Assert.That(target, Is.Not.Null);
        Assert.That(target, Is.EqualTo(todayString));
    }

    /// <summary>
    /// Provided by RyogoNA in issue #38 https://github.com/ServiceStack/ServiceStack.OrmLite/issues/38#issuecomment-4625178
    /// </summary>
    [Alias("Analyzes")]
    public class Analyze : IHasId<int>
    {
        [AutoIncrement]
        [PrimaryKey]
        public int Id
        {
            get;
            set;
        }
        [Alias("AnalyzeDate")]
        public DateTime Date
        {
            get;
            set;
        }
        public string Url
        {
            get;
            set;
        }
    }
}